<?php
/**
 * Created by PhpStorm.
 * User: qwert
 * Date: 2016/11/22
 * Time: 18:05
 */
function getUserByUid($uid){
      $ModAct = M('user','ny_','newyear');
      $where['id']=$uid;
      $data=$ModAct->where($where)->find();
      return $data;
}


function getDuiHuanPrizeByPid($pid){
      $ModAct = M('exchange','ny_prize_','newyear');
      $where['id']=$pid;
      $data=$ModAct->where($where)->find();
      return $data;
}

function getLottoPrizeByPid($pid){
      $ModAct = M('lotto','ny_prize_','newyear');
      $where['id']=$pid;
      $data=$ModAct->where($where)->find();
      return $data;
}

function  jisuanScore($uid){
      $M = M('prize','ny_act_','newyear');
      $where['uid']=$uid;
      $prizeList= $M->where($where)->select(); //该用户所有奖品
      $allScore=0;
      foreach($prizeList as $value){
            $score=getScore($value['pid']);
            $allScore=$allScore+$score;
      }
      return $allScore;
}

function  jisuanAllScore(){
      $M = M('prize','ny_act_','newyear');
      $prizeList= $M->where("type=1")->select();
      $allScore=0;
      foreach($prizeList as $value){
            $score=getScore($value['pid']);
            $allScore=$allScore+$score;
      }
      return $allScore;
}
function  jisuanPrize($uid){
      $M = M('prize','ny_act_','newyear');
      $prizeList= $M->where('uid='.$uid.' and pid not in (7,8,12,13) and type=1')->select(); //该用户所有奖品
//      $M2 = M('exchange','ny_prize_','newyear');
      $allprize="";
      foreach($prizeList as $value){
            $prize=GetMyPrizeBypid($value['pid']);
            $allprize.=$prize['pname']."-";
      }
      return $allprize;
}
function  jisuanPrizeQuan($uid){
      $M = M('prize','ny_act_','newyear');
      $prizeList= $M->where('uid='.$uid.' and pid  in (7,8,12,13) and type=1')->select(); //该用户所有奖品
//      $M2 = M('exchange','ny_prize_','newyear');
      $allprize="";
      foreach($prizeList as $value){
            $prize=GetMyPrizeBypid($value['pid']);
            $allprize.=$prize['pname'].$value['code_url'].$value['code']."---";
      }
      return $allprize;
}

function jisuanLottoPrize($uid){
      $M = M('prize','ny_act_','newyear');
      $prizeList= $M->where('uid='.$uid.' and pid not in (6,7,8,9,10) and type=2')->select(); //该用户所有奖品
//      $M2 = M('exchange','ny_prize_','newyear');
      $allprize="";
      foreach($prizeList as $value){
            $prize=GetMyLottoPrizeBypid($value['pid']);
            $allprize.=$prize['pname']."-";
      }
      return $allprize;
}
function jisuanLottoPrizeQuan($uid){
      $M = M('prize','ny_act_','newyear');
      $prizeList= $M->where('uid='.$uid.' and pid  in (6,7,8,9,10) and type=2')->select(); //该用户所有奖品
//      $M2 = M('exchange','ny_prize_','newyear');
      $allprize="";
      foreach($prizeList as $value){
            $prize=GetMyLottoPrizeBypid($value['pid']);
            $allprize.=$prize['pname'].$value['code_url'].$value['code']."---";
      }
      return $allprize;
}
/**
 * 获取 用户 奖品
 * @param pid
 */
  function  GetMyPrizeBypid($pid){
      $M = M('exchange','ny_prize_','newyear');
      $where['id'] = $pid;
      // 把查询条件传入查询方法
      $result= $M->where($where)->find();
      return $result;
}
/**
 * 获取 用户 奖品
 * @param pid
 */
function  GetMyLottoPrizeBypid($pid){
      $M = M('lotto','ny_prize_','newyear');
      $where['id'] = $pid;
      // 把查询条件传入查询方法
      $result= $M->where($where)->find();
      return $result;
}
function  getScore($pid){
      $M2 = M('exchange','ny_prize_','newyear');
//      $where['id']=$pid;
      $prize= $M2->where("id=".$pid)->find();
      return $prize['score'];
}
/**
 * 通过uid
 * 获取用户的积分
 * @param uid $uid
 * @return userScore
 */
function GetUserSocreByUid($uid){

      $M = M('point','e_user_','ecrm');
      $where['user_id'] = $uid;
      // 把查询条件传入查询方法
      $point= $M->where($where)->find();
      if($point!=null || $point!=""){
            return $point['currency_point'];
      }else{
            return "";
      }
}

function GetUserSignCount($uid){
      $M = M('sign','ny_','newyear');
      $where['uid']=$uid;
      $U=$M->where($where)->select();
      return count($U);
}
/**
 * 通过uid
 * 获取用户的积分
 * @param uid $uid
 * @return userScore
 */
function GetUserTotalSocreByUid($uid){

      $M = M('point','e_user_','ecrm');
      $where['user_id'] = $uid;
      // 把查询条件传入查询方法
      $point= $M->where($where)->find();
      if($point!=null || $point!=""){
            return $point['total_currency'];
      }else{
            return 0;
      }
}

function create_csv($data,$header=null,$filename='simple.csv'){
      // 如果手动设置表头；则放在第一行
      if (!is_null($header)) {
            array_unshift($data, $header);
      }
      // 防止没有添加文件后缀
      $filename=str_replace('.csv', '', $filename).'.csv';
      ob_clean();
      Header( "Content-type:  application/octet-stream ");
      Header( "Accept-Ranges:  bytes ");
      Header( "Content-Disposition:  attachment;  filename=".$filename);
      foreach( $data as $k => $v){
            // 如果是二维数组；转成一维
            if (is_array($v)) {
                  $v=implode(',', $v);
            }
            // 替换掉换行
            $v=preg_replace('/\s*/', '', $v);
            // 解决导出的数字会显示成科学计数法的问题
            $v=str_replace(',', "\t,", $v);
            // 转成gbk以兼容office乱码的问题
            echo iconv('UTF-8','GBK',$v)."\t\r\n";
      }
}

/**
 * 数组转xls格式的excel文件
 * @param  array  $data      需要生成excel文件的数组
 * @param  string $filename  生成的excel文件名
 *      示例数据：
$data = array(
array(NULL, 2010, 2011, 2012),
array('Q1',   12,   15,   21),
array('Q2',   56,   73,   86),
array('Q3',   52,   61,   69),
array('Q4',   30,   32,    0),
);
 */

function create_PrizeQuan_xls($data,$header,$filename='ExportUser.xls'){

      ini_set('max_execution_time', '0');
      import('Vendor.PHPExcel.PHPExcel');
      $filename=str_replace('.xls', '', $filename).'.xls';
      $phpexcel = new \PHPExcel();
      $phpexcel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("兑换奖品导出列表")
            ->setSubject("兑换奖品导出列表")
            ->setDescription("兑换奖品导出列表.")
            ->setKeywords("兑换奖品导出列表")
            ->setCategory("兑换奖品导出列表");
      $sheet=$phpexcel->setActiveSheetIndex(0);
//      foreach($header as $key=>$value){
//            $sheet->setCellValue($key,$value[$key]);
//      }
      $sheet->setCellValue( 'A1', "uid" )
            ->setCellValue( 'B1', "邮箱" )
            ->setCellValue( 'C1', "昵称" )
            ->setCellValue( 'D1', "姓名" )
            ->setCellValue( 'E1', "手机号" )
            ->setCellValue( 'F1', "兑换券" );
      // 给表格添加数据
      foreach ($data as $k => $v) {
            $i = $k + 2;
            $id = $v['id'];
            $email = $v['email'];
            $user_name = $v['user_name'];
            $user_turename = $v['user_turename'];
            $phone = $v['phone'];
            $address = $v['address'];
            $socre = $v['socre'];
            $prizelist = $v['prizelist'];
            $sheet->setCellValue( "A".$i, "$id");
            $sheet->setCellValue( "B".$i, "$email");
            $sheet->setCellValue( "C".$i, "$user_name");
            $sheet->setCellValue( "D".$i, "$user_turename");
            $sheet->setCellValue( "E".$i, "$phone");
//            $sheet->setCellValue( "F".$i, "$address");
            $sheet->setCellValue( "F".$i, "$prizelist");
      }
//      $phpexcel->getActiveSheet()->fromArray($data);
      $phpexcel->getActiveSheet()->setTitle('兑换奖品导出列表');

      header('Content-Type: application/vnd.ms-excel');
      header("Content-Disposition: attachment;filename=$filename");
      header('Cache-Control: max-age=0');
      header('Cache-Control: max-age=1');
      header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
      header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
      header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
      header ('Pragma: public'); // HTTP/1.0
      $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
      $objwriter->save('php://output');
      exit;

}

function create_xls($data,$header,$filename='ExportUser.xls'){
      ini_set('max_execution_time', '0');
      import('Vendor.PHPExcel.PHPExcel');
      $filename=str_replace('.xls', '', $filename).'.xls';
      $phpexcel = new \PHPExcel();
      $phpexcel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("兑换奖品导出列表")
            ->setSubject("兑换奖品导出列表")
            ->setDescription("兑换奖品导出列表.")
            ->setKeywords("兑换奖品导出列表")
            ->setCategory("兑换奖品导出列表");
      $sheet=$phpexcel->setActiveSheetIndex(0);
//      foreach($header as $key=>$value){
//            $sheet->setCellValue($key,$value[$key]);
//      }
      $sheet->setCellValue( 'A1', "uid" )
            ->setCellValue( 'B1', "邮箱" )
            ->setCellValue( 'C1', "昵称" )
            ->setCellValue( 'D1', "姓名" )
            ->setCellValue( 'E1', "手机号" )
            ->setCellValue( 'F1', "详细地址" )
            ->setCellValue( 'G1', "消耗总积分" )
            ->setCellValue( 'H1', "所有奖品" );
      // 给表格添加数据
      foreach ($data as $k => $v) {
            $i = $k + 2;
            $id = $v['id'];
            $email = $v['email'];
            $user_name = $v['user_name'];
            $user_turename = $v['user_turename'];
            $phone = $v['phone'];
            $address = $v['address'];
            $socre = $v['socre'];
            $prizelist = $v['prizelist'];
            $sheet->setCellValue( "A".$i, "$id");
            $sheet->setCellValue( "B".$i, "$email");
            $sheet->setCellValue( "C".$i, "$user_name");
            $sheet->setCellValue( "D".$i, "$user_turename");
            $sheet->setCellValue( "E".$i, "$phone");
            $sheet->setCellValue( "F".$i, "$address");
            $sheet->setCellValue( "G".$i, "$socre");
            $sheet->setCellValue( "H".$i, "$prizelist");
      }
//      $phpexcel->getActiveSheet()->fromArray($data);
        $phpexcel->getActiveSheet()->setTitle('兑换奖品导出列表');

      header('Content-Type: application/vnd.ms-excel');
      header("Content-Disposition: attachment;filename=$filename");
      header('Cache-Control: max-age=0');
      header('Cache-Control: max-age=1');
      header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
      header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
      header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
      header ('Pragma: public'); // HTTP/1.0
      $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
      $objwriter->save('php://output');
      exit;
}

function create_lotto_xls($data,$header,$filename='ExportUser.xls'){
      ini_set('max_execution_time', '0');
      import('Vendor.PHPExcel.PHPExcel');
      $filename=str_replace('.xls', '', $filename).'.xls';
      $phpexcel = new \PHPExcel();
      $phpexcel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("兑换奖品导出列表")
            ->setSubject("兑换奖品导出列表")
            ->setDescription("兑换奖品导出列表.")
            ->setKeywords("兑换奖品导出列表")
            ->setCategory("兑换奖品导出列表");
      $sheet=$phpexcel->setActiveSheetIndex(0);
//      foreach($header as $key=>$value){
//            $sheet->setCellValue($key,$value[$key]);
//      }
      $sheet->setCellValue( 'A1', "uid" )
            ->setCellValue( 'B1', "邮箱" )
            ->setCellValue( 'C1', "昵称" )
            ->setCellValue( 'D1', "姓名" )
            ->setCellValue( 'E1', "手机号" )
            ->setCellValue( 'F1', "详细地址" )
//            ->setCellValue( 'G1', "消耗总积分" )
            ->setCellValue( 'G1', "所有奖品" );
      // 给表格添加数据
      foreach ($data as $k => $v) {
            $i = $k + 2;
            $id = $v['id'];
            $email = $v['email'];
            $user_name = $v['user_name'];
            $user_turename = $v['user_turename'];
            $phone = $v['phone'];
            $address = $v['address'];
//            $socre = $v['socre'];
            $prizelist = $v['prizelist'];
            $sheet->setCellValue( "A".$i, "$id");
            $sheet->setCellValue( "B".$i, "$email");
            $sheet->setCellValue( "C".$i, "$user_name");
            $sheet->setCellValue( "D".$i, "$user_turename");
            $sheet->setCellValue( "E".$i, "$phone");
            $sheet->setCellValue( "F".$i, "$address");
//            $sheet->setCellValue( "G".$i, "$socre");
            $sheet->setCellValue( "G".$i, "$prizelist");
      }
//      $phpexcel->getActiveSheet()->fromArray($data);
      $phpexcel->getActiveSheet()->setTitle('兑换奖品导出列表');

      header('Content-Type: application/vnd.ms-excel');
      header("Content-Disposition: attachment;filename=$filename");
      header('Cache-Control: max-age=0');
      header('Cache-Control: max-age=1');
      header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
      header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
      header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
      header ('Pragma: public'); // HTTP/1.0
      $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
      $objwriter->save('php://output');
      exit;

}
function create_User_xls($data,$header,$filename='Export_lotto_quan.xls'){

      ini_set('max_execution_time', '0');
      import('Vendor.PHPExcel.PHPExcel');
      $filename=str_replace('.xls', '', $filename).'.xls';
      $phpexcel = new \PHPExcel();
      $phpexcel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("用户导出列表")
            ->setSubject("用户导出列表")
            ->setDescription("用户导出列表.")
            ->setKeywords("用户导出列表")
            ->setCategory("用户导出列表");
      $sheet=$phpexcel->setActiveSheetIndex(0);
//      foreach($header as $key=>$value){
//            $sheet->setCellValue($key,$value[$key]);
//      }
      $sheet->setCellValue( 'A1', "uid" )
            ->setCellValue( 'B1', "邮箱" )
            ->setCellValue( 'C1', "昵称" )
            ->setCellValue( 'D1', "姓名" )
            ->setCellValue( 'E1', "手机号" )
            ->setCellValue( 'F1', "详细地址" )
            ->setCellValue( 'G1', "当前积分" )
            ->setCellValue( 'H1', "消耗积分" );
      // 给表格添加数据
      foreach ($data as $k => $v) {
            $i = $k + 2;
            $id = $v['id'];
            $email = $v['email'];
            $user_name = $v['user_name'];
            $user_turename = $v['user_turename'];
            $phone = $v['phone'];
            $address = $v['address'];
            $currentScore = $v['currentScore'];
            $UserScore = $v['UsedScore'];
            $sheet->setCellValue( "A".$i, "$id");
            $sheet->setCellValue( "B".$i, "$email");
            $sheet->setCellValue( "C".$i, "$user_name");
            $sheet->setCellValue( "D".$i, "$user_turename");
            $sheet->setCellValue( "E".$i, "$phone");
            $sheet->setCellValue( "F".$i, "$address");
            $sheet->setCellValue( "G".$i, "$currentScore");
            $sheet->setCellValue( "H".$i, "$UserScore");
      }
//      $phpexcel->getActiveSheet()->fromArray($data);
      $phpexcel->getActiveSheet()->setTitle('用户列表');

      header('Content-Type: application/vnd.ms-excel');
      header("Content-Disposition: attachment;filename=$filename");
      header('Cache-Control: max-age=0');
      header('Cache-Control: max-age=1');
      header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
      header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
      header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
      header ('Pragma: public'); // HTTP/1.0
      $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
      $objwriter->save('php://output');
      exit;

}


function create_lottoQuan_xls($data,$header,$filename='Export_lotto_quan.xls'){
      ini_set('max_execution_time', '0');
      import('Vendor.PHPExcel.PHPExcel');
      $filename=str_replace('.xls', '', $filename).'.xls';
      $phpexcel = new \PHPExcel();
      $phpexcel->getProperties()
            ->setCreator("admin")
            ->setLastModifiedBy("admin")
            ->setTitle("兑换奖品导出列表")
            ->setSubject("兑换奖品导出列表")
            ->setDescription("兑换奖品导出列表.")
            ->setKeywords("兑换奖品导出列表")
            ->setCategory("兑换奖品导出列表");
      $sheet=$phpexcel->setActiveSheetIndex(0);
//      foreach($header as $key=>$value){
//            $sheet->setCellValue($key,$value[$key]);
//      }
      $sheet->setCellValue( 'A1', "uid" )
            ->setCellValue( 'B1', "邮箱" )
            ->setCellValue( 'C1', "昵称" )
            ->setCellValue( 'D1', "姓名" )
            ->setCellValue( 'E1', "手机号" )
//            ->setCellValue( 'F1', "详细地址" )
////            ->setCellValue( 'G1', "消耗总积分" )
            ->setCellValue( 'F1', "所有奖品" );
      // 给表格添加数据
      foreach ($data as $k => $v) {
            $i = $k + 2;
            $id = $v['id'];
            $email = $v['email'];
            $user_name = $v['user_name'];
            $user_turename = $v['user_turename'];
            $phone = $v['phone'];
            $address = $v['address'];
//            $socre = $v['socre'];
            $prizelist = $v['prizelist'];
            $sheet->setCellValue( "A".$i, "$id");
            $sheet->setCellValue( "B".$i, "$email");
            $sheet->setCellValue( "C".$i, "$user_name");
            $sheet->setCellValue( "D".$i, "$user_turename");
            $sheet->setCellValue( "E".$i, "$phone");
            $sheet->setCellValue( "F".$i, "$prizelist");
      }
//      $phpexcel->getActiveSheet()->fromArray($data);
      $phpexcel->getActiveSheet()->setTitle('兑换奖品导出列表');

      header('Content-Type: application/vnd.ms-excel');
      header("Content-Disposition: attachment;filename=$filename");
      header('Cache-Control: max-age=0');
      header('Cache-Control: max-age=1');
      header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
      header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
      header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
      header ('Pragma: public'); // HTTP/1.0
      $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
      $objwriter->save('php://output');
      exit;


}